Declare @UEN varchar(20), @EntityName varchar(20), @CustomerName varchar(20), @ContactNo varchar(20)

SET @EntityName = 'ACRA'
Declare @TmpEntityName varchar(20)
SET  @TmpEntityName = @EntityName
SET @EntityName = '"*' + @TmpEntityName + '*"'

SELECT *
FROM tblEntity
WHERE CONTAINS(EntityName, @EntityName);

SET @EntityName = '%'+ @TmpEntityName + '%'
SELECT *
FROM tblEntity
WHERE EntityName LIKE @EntityName

Select E.EntityID, ContactProfileID, E.EntityName, ET.Description, E.UEN, 
			CP.Salutation + ' ' + CP.SurName + ' ' + CP.Name, 
			CP.Office, CP.Mobile, CP.Residential, CP.Email, CP.BNInfo2 FROM
			tblContactProfile CP INNER JOIN tblEntity E  ON E.EntityID = CP.EntityID
			INNER JOIN tblEntityTypeInfo ET ON E.EntityTypeID = ET.EntityTypeID
			WHERE CP.BNInfo2 IS NOT NULL
			And (@EntityName IS NULL OR E.EntityName LIKE '%' + @EntityName + '%')
			AND (@UEN IS NULL OR E.UEN LIKE '%' + @UEN + '%')
			AND (@CustomerName IS NULL OR (CP.SurName LIKE '%' + @CustomerName + '%') OR (CP.Name + ' ' + CP.SurName LIKE '%' + @CustomerName + '%') OR (CP.SurName + ' ' + CP.Name LIKE '%' + @CustomerName + '%'))
			AND (@ContactNo IS NULL OR ( 
											(ISNUMERIC(@ContactNo) = 1 And ((CP.Mobile LIKE '%' + @ContactNo + '%') OR (CP.Office LIKE '%' + @ContactNo + '%') OR (CP.Residential LIKE '%' + @ContactNo + '%')))
											OR (ISNUMERIC(@ContactNo) = 0 And CP.Email LIKE '%' + @ContactNo + '%')
										)
				)


Select E.EntityID, ContactProfileID, E.EntityName, ET.Description, E.UEN, 
			CP.Salutation + ' ' + CP.SurName + ' ' + CP.Name, 
			CP.Office, CP.Mobile, CP.Residential, CP.Email, CP.BNInfo2 FROM
			tblContactProfile CP INNER JOIN tblEntity E  ON E.EntityID = CP.EntityID
			INNER JOIN tblEntityTypeInfo ET ON E.EntityTypeID = ET.EntityTypeID
			WHERE CP.BNInfo2 IS NOT NULL
			And (@EntityName IS NULL OR CONTAINS(EntityName, @EntityName))
			AND (@UEN IS NULL OR CONTAINS(E.UEN, @UEN))
			AND (@CustomerName IS NULL OR (CONTAINS(CP.SurName, @CustomerName) OR (CONTAINS((CP.Name + ' ' + CP.SurName), @CustomerName)) OR (CP.SurName + ' ' + CP.Name LIKE '%' + @CustomerName + '%'))
			AND (@ContactNo IS NULL OR ( 
											(ISNUMERIC(@ContactNo) = 1 And ((CP.Mobile LIKE '%' + @ContactNo + '%') OR (CP.Office LIKE '%' + @ContactNo + '%') OR (CP.Residential LIKE '%' + @ContactNo + '%')))
											OR (ISNUMERIC(@ContactNo) = 0 And CP.Email LIKE '%' + @ContactNo + '%')
										)
				)